﻿
CREATE PROCEDURE [dbo].[resuma_TransferDB]
	-- Add the parameters for the stored procedure here
	@ServerName varchar(50), 
	@DBName varchar(50),
	@StergeDateAnterioare bit,
	@UnitateNoua smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @comanda varchar(5000)
    DECLARE @strCodUnitate varchar(10)

	BEGIN TRAN

	IF  @StergeDateAnterioare = 1
	BEGIN    
		SET @Comanda = 'DELETE FROM [AngajatiOcupatii]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
	    
		SET @Comanda = 'DELETE FROM [AngajatiSalarizare]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [Pontaj]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [PolitaAsigurare]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [SituatieMilitara]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [PersoaneSuspCM]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [RaportareFoiBoala]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [SpecialitatiPersoane]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [Sporuri]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [Consultatii]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

		SET @Comanda = 'DELETE FROM [ContracteMunca]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	


		SET @Comanda = 'DELETE FROM [Persoane]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		
		SET @Comanda = 'DELETE FROM [TipSpor]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipSpecialitati]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipStudii]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipPrezenta]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipGrad]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipContracte]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipAdaos]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipGradMilitar]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	
		SET @Comanda = 'DELETE FROM [TipStareMilitara]'
		EXEC(@Comanda) 
		IF @@ERROR != 0 GOTO EROARE	

	    
	END
	PRINT('Sterge Tabele - ok')

	SET IDENTITY_INSERT [TipStareMilitara] ON
	SET @Comanda = 'INSERT INTO [TipStareMilitara] (IdStareMilitara,Denumire)
			SELECT IdStareMilitara,Denumire FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipStareMilitara]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipStareMilitara] OFF
	PRINT('TipStareMilitara - ok')

	
	SET IDENTITY_INSERT [TipGradMilitar] ON
	SET @Comanda = 'INSERT INTO [TipGradMilitar] (IdTipgradMilitar,Denumire)
			SELECT IdTipgradMilitar,Denumire FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipGradMilitar]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipGradMilitar] OFF
	PRINT('TipGradMilitar - ok')

	
	SET @Comanda = 'INSERT INTO [TipAdaos] ([Nume],[IdAdaos],[Descriere],[NumaiBrut],[SeImpoziteaza],[DinCAS],[IdOpContabila],[SeImpoziteazaSeparat])
			SELECT [Nume],[IdAdaos],[Descriere],[NumaiBrut],[SeImpoziteaza],[DinCAS],[IdOpContabila],[SeImpoziteazaSeparat] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipAdaos]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('TipAdaos - ok')

	SET IDENTITY_INSERT TipContracte ON
	SET @Comanda = 'INSERT INTO [TipContracte] ([TipContract],[Cod],[Denumire])
			SELECT [TipContract],[Cod],[Denumire] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipContracte]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipContracte] OFF
	PRINT('TipContracte - ok')

	SET IDENTITY_INSERT [TipGrad] ON
	SET @Comanda = 'INSERT INTO [TipGrad] ([IdTipGrad],[Cod],[Descriere])
			SELECT [IdTipGrad],[Cod],[Descriere] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipGrad]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipGrad] OFF
	PRINT('TipGrad - ok')


	SET @Comanda = 'INSERT INTO [TipPrezenta] ([CodPrezenta],[CodListare],[Ordonare],[TimpBaza],[IdTipPrezenta],[Descriere],[DataDefinire],[Selectat],[SeListeaza],[IdOpContabila])
			SELECT [CodPrezenta],[CodListare],[Ordonare],[TimpBaza],[IdTipPrezenta],[Descriere],[DataDefinire],[Selectat],[SeListeaza],[IdOpContabila] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipPrezenta]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('TipPrezenta - ok')

	SET IDENTITY_INSERT [TipStudii] ON
	SET @Comanda = 'INSERT INTO [TipStudii] ([IdTipStudii],[Cod],[Descriere])
			SELECT [IdTipStudii],[Cod],[Descriere] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipStudii]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipStudii] OFF
	PRINT('TipStudii - ok')

	SET IDENTITY_INSERT [TipSpecialitati] ON
	SET @Comanda = 'INSERT INTO [TipSpecialitati] (IdSpecialitate,Denumire)
			SELECT IdSpecialitate,Denumire FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipSpecialitati]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipSpecialitati] OFF
	PRINT('TipSpecialitati - ok')
	
	SET IDENTITY_INSERT [TipSpor] ON
	SET @Comanda = 'INSERT INTO [TipSpor] ([IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[CodGrila],[Indemnizatie])
			SELECT [IDTipSpor],[Nume],[Descriere],[Valoare],[Format],[Utilizat],[Ordonare],[IntraIn],[ConcediuOdihna],[ConcediuBoala],[Permanent],[Activ],[DataDefinire],[IdOpContabila],[ConcediuOreSuplimentare],[CodGrila],[Indemnizatie] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[TipSpor]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [TipSpor] OFF
	PRINT('TipSpor - ok')


	SET IDENTITY_INSERT [Judete] ON
	SET @Comanda = 'INSERT INTO [Judete] ([IDJudet]
      ,[Nume]
      ,[Sigla]
      ,[PrefixTelefonic]
      ,[ResedintaJudet])
			SELECT [IDJudet]
      ,[Nume]
      ,[Sigla]
      ,[PrefixTelefonic]
      ,[ResedintaJudet] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Judete] a WHERE NOT EXISTS(SELECT * FROM [Judete] WHERE a.IDJudet=IDJudet)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Judete] OFF
	PRINT('Judete - ok')


	SET IDENTITY_INSERT [Localitati] ON
	SET @Comanda = 'INSERT INTO [Localitati] ([IDLocalitate]
      ,[Nume]
      ,[CodPostal]
      ,[IDJudet]
      ,[PrefixTelefonic])
	SELECT [IDLocalitate]
      ,[Nume]
      ,[CodPostal]
      ,[IDJudet]
      ,[PrefixTelefonic] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Localitati] a WHERE NOT EXISTS(SELECT * FROM [Localitati] WHERE a.IDLocalitate=IDLocalitate)'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Localitati] OFF
	PRINT('Localitati - ok')



	SET IDENTITY_INSERT [Persoane] ON
	SET @Comanda = 'INSERT INTO [Persoane] ([IdPersoana]
      ,[NumePrenume]
      ,[Nume_Anterior]
      ,[Cnp]
      ,[Sex]
      ,[DataNasterii]
      ,[IDLocNastere]
      ,[TipAI]
      ,[SeriaAI]
      ,[NumarAI]
      ,[DataElibAI]
      ,[EmitentAI]
      ,[NumeTata]
      ,[NumeMama]
      ,[StradaAdr]
      ,[IDLocalitateAdr]
      ,[NrAdr]
      ,[BlocAdr]
      ,[EtajAdr]
      ,[ScaraAdr]
      ,[ApartamentAdr]
      ,[GradInvaliditate]
      ,[TelefonFix]
      ,[TelefonMobil]
      ,[Observatii]
      ,[NumarCM]
      ,[SerieCM]
      ,[CodUnitate]
      ,[NrFileCM]
      ,[NrCutieCM]
      ,[PozCutieCM]
      ,[Marca]
      ,[TelefonInterior]
      ,[IdCasaAsigSanatate]
      ,[IdTipStudii]
      ,[DataVechimeMunca]
      ,[UserID]
      ,[DataPlecarii])
	SELECT [IdPersoana]
      ,[NumePrenume]
      ,[Nume_Anterior]
      ,[Cnp]
      ,[Sex]
      ,[DataNasterii]
      ,[IDLocNastere]
      ,[TipAI]
      ,[SeriaAI]
      ,[NumarAI]
      ,[DataElibAI]
      ,[EmitentAI]
      ,[NumeTata]
      ,[NumeMama]
      ,[StradaAdr]
      ,[IDLocalitateAdr]
      ,[NrAdr]
      ,[BlocAdr]
      ,[EtajAdr]
      ,[ScaraAdr]
      ,[ApartamentAdr]
      ,[GradInvaliditate]
      ,[TelefonFix]
      ,[TelefonMobil]
      ,[Observatii]
      ,[NumarCM]
      ,[SerieCM]'
      IF @UnitateNoua < 0
		SET @comanda = @comanda + ',[CodUnitate]'
	  ELSE	
		SET @comanda = @comanda + ',' + CAST(@unitateNoua as varchar(10))
      SET @comanda = @comanda + ',[NrFileCM]
      ,[NrCutieCM]
      ,[PozCutieCM]
      ,[Marca]
      ,[TelefonInterior]
      ,[IdCasaAsigSanatate]
      ,[IdTipStudii]
      ,[DataVechimeMunca]
      ,[UserID]
      ,[DataPlecarii] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Persoane]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Persoane] OFF
	PRINT('Persoane - ok')


	SET IDENTITY_INSERT [ContracteMunca] ON
	SET @Comanda = 'INSERT INTO [ContracteMunca] ([IdContract]
      ,[IdPersoana]
      ,[Numar]
      ,[Data]
      ,[PerioadaScadenta]
      ,[TipContract]
      ,[DeLaData]
      ,[PanaLaData]
      ,[PerioadaDeterminata]
      ,[IDOcupatie]
      ,[TipGrad]
      ,[SalarIncadrare]
      ,[Observatii]
      ,[CodUnitate]
      ,[OreNorma]
      ,[FractiuneNorma])
	SELECT [IdContract]
      ,[IdPersoana]
      ,[Numar]
      ,[Data]
      ,[PerioadaScadenta]
      ,[TipContract]
      ,[DeLaData]
      ,[PanaLaData]
      ,[PerioadaDeterminata]
      ,[IDOcupatie]
      ,[TipGrad]
      ,[SalarIncadrare]
      ,[Observatii]'
      IF @UnitateNoua < 0
		SET @comanda = @comanda + ',[CodUnitate]'
	  ELSE	
		SET @comanda = @comanda + ',' + CAST(@unitateNoua as varchar(10))
      SET @comanda = @comanda + ',[OreNorma]
      ,[FractiuneNorma] FROM ['+@ServerName+'].['+@DBName+'].[dbo].[ContracteMunca]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [ContracteMunca] OFF
	PRINT('ContracteMunca - ok')

	/*SET IDENTITY_INSERT [Consultatii] ON
	SET @Comanda = 'INSERT INTO [Consultatii] ([IdCons]
      ,[IdPersoana]
      ,[Data]
      ,[Motivele_prezentarii]
      ,[Ex_clinic]
      ,[Ecografie]
      ,[Cistoscopie]
      ,[Diagnostic]
      ,[Reteta]
      ,[Recomandare])
	SELECT [IdCons]
      ,[IdPersoana]
      ,[Data]
      ,[Motivele_prezentarii]
      ,[Ex_clinic]
      ,[Ecografie]
      ,[Cistoscopie]
      ,[Diagnostic]
      ,[Reteta]
      ,[Recomandare]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Consultatii]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Consultatii] OFF
	PRINT('Consultatii - ok')*/

	SET IDENTITY_INSERT [Sporuri] ON
	SET @Comanda = 'INSERT INTO [Sporuri] ([Valoare]
      ,[ValoareCo]
      ,[ValoareInt]
      ,[Procent]
      ,[ValoarePreaviz]
      ,[IdTipSpor]
      ,[IdPersoana]
      ,[DataDefinire]
      ,[IdDepartament],IDSporPersoana,PanaLaData)
	SELECT [Valoare]
      ,[ValoareCo]
      ,[ValoareInt]
      ,[Procent]
      ,[ValoarePreaviz]
      ,[IdTipSpor]
      ,[IdPersoana]
      ,[DataDefinire]
      ,[IdDepartament],IDSporPersoana,PanaLaData
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Sporuri]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Sporuri] OFF
	PRINT('Sporuri - ok')
	
	SET IDENTITY_INSERT [SpecialitatiPersoane] ON
	SET @Comanda = 'INSERT INTO [SpecialitatiPersoane] ([IDPersSpecialitate]
      ,[IdPersoana]
      ,[DataObtinere]
      ,[LuniVechime]
      ,[IDSpecialitate]
      ,[Principala])
	SELECT [IDPersSpecialitate]
      ,[IdPersoana]
      ,[DataObtinere]
      ,[LuniVechime]
      ,[IDSpecialitate]
      ,[Principala]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[SpecialitatiPersoane]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [SpecialitatiPersoane] OFF
	PRINT('SpecialitatiPersoane - ok')	
	
	SET IDENTITY_INSERT [RaportareFoiBoala] ON
	SET @Comanda = 'INSERT INTO [RaportareFoiBoala] ([IDFoaieBoala]
      ,[IDPersoana]
      ,[MedicPrescriptor]
      ,[UnitateaSanitaraMedic]
      ,[DataAnuntare]
      ,[DataUpdate])
	SELECT [IDFoaieBoala]
      ,[IDPersoana]
      ,[MedicPrescriptor]
      ,[UnitateaSanitaraMedic]
      ,[DataAnuntare]
      ,[DataUpdate]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[RaportareFoiBoala]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [RaportareFoiBoala] OFF
	PRINT('RaportareFoiBoala - ok')	
	
	SET IDENTITY_INSERT [PersoaneSuspCM] ON
	SET @Comanda = 'INSERT INTO [PersoaneSuspCM] ([IDSuspendare]
      ,[DeLaData]
      ,[PanaLaData]
      ,[IDPersoana])
	SELECT [IDSuspendare]
      ,[DeLaData]
      ,[PanaLaData]
      ,[IDPersoana]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[PesoaneSuspCM]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [PersoaneSuspCM] OFF
	PRINT('PersoaneSuspCM - ok')	
	
	SET @Comanda = 'INSERT INTO [SituatieMilitara] ([IdPersoana]
      ,[IdGradMilitar]
      ,[Specialitatea]
      ,[Pozitia]
      ,[IdStareMilitara]
      ,[DataScoatereEvidenta]
      ,[DataLuatEvidenta]
      ,[SeriaLM]
      ,[NumarLM]
      ,[LocLM]
      ,[CorpMilitar])
	SELECT [IdPersoana]
      ,[IdGradMilitar]
      ,[Specialitatea]
      ,[Pozitia]
      ,[IdStareMilitara]
      ,[DataScoatereEvidenta]
      ,[DataLuatEvidenta]
      ,[SeriaLM]
      ,[NumarLM]
      ,[LocLM]
      ,[CorpMilitar]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[SituatieMilitara]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	PRINT('SituatieMilitara - ok')	
	
	SET IDENTITY_INSERT [Pontaj] ON
	SET @Comanda = 'INSERT INTO [Pontaj] ([IdPontaj]
      ,[Tip]
      ,[Valoare]
      ,[TipAcord]
      ,[KAcord]
      ,[IdPersoana]
      ,[IdTipPrezenta]
      ,[IdDepartament]
      ,[DataDefinire]
      ,[SalarOrar]
      ,[CodUnitate]
      ,[Luna]
      ,[Anul])
	SELECT [IdPontaj]
      ,[Tip]
      ,[Valoare]
      ,[TipAcord]
      ,[KAcord]
      ,[IdPersoana]
      ,[IdTipPrezenta]
      ,[IdDepartament]
      ,[DataDefinire]
      ,[SalarOrar]'
      IF @UnitateNoua < 0
		SET @comanda = @comanda + ',[CodUnitate]'
	  ELSE	
		SET @comanda = @comanda + ',' + CAST(@unitateNoua as varchar(10))
      SET @comanda = @comanda + ',[Luna]
      ,[Anul]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[Pontaj]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [Pontaj] OFF
	PRINT('Pontaj - ok')	
	
	SET IDENTITY_INSERT [AngajatiSalarizare] ON
	SET @Comanda = 'INSERT INTO [AngajatiSalarizare] ([IDAngajatSalarizare]
      ,[IdPersoana]
      ,[DeLaData]
      ,[Salar]
      ,[IDDepartament]
      ,[CodUnitate]
      ,[IdTipGrad]
      ,[OreNorma]
      ,[FractiuneNorma])
	SELECT [IDAngajatSalarizare]
      ,[IdPersoana]
      ,[DeLaData]
      ,[Salar]
      ,[IDDepartament]'
      IF @UnitateNoua < 0
		SET @comanda = @comanda + ',[CodUnitate]'
	  ELSE	
		SET @comanda = @comanda + ',' + CAST(@unitateNoua as varchar(10))
      SET @comanda = @comanda + ',[IdTipGrad]
      ,[OreNorma]
      ,[FractiuneNorma]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[AngajatiSalarizare]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [AngajatiSalarizare] OFF
	PRINT('AngajatiSalarizare - ok')	
	
	SET IDENTITY_INSERT [AngajatiOcupatii] ON
	SET @Comanda = 'INSERT INTO [AngajatiOcupatii] ([IDOcupatieAngajat]
      ,[IDOcupatie]
      ,[DeLaData]
      ,[PanaLaData]
      ,[IDPersoana])
	SELECT [IDOcupatieAngajat]
      ,[IDOcupatie]
      ,[DeLaData]
      ,[PanaLaData]
      ,[IDPersoana]
	FROM ['+@ServerName+'].['+@DBName+'].[dbo].[AngajatiOcupatii]'
	EXEC(@Comanda) 
	IF @@ERROR != 0 GOTO EROARE	
	SET IDENTITY_INSERT [AngajatiOcupatii] OFF
	PRINT('AngajatiOcupatii - ok')	
	
	
 	COMMIT



	GOTO Gata
	EROARE:
		ROLLBACK
		RAISERROR ('Eroare la preluarea datelor!',16,-1)
		GOTO Gata
	EROARE1:
		RAISERROR ('!!',16,-1)	
		GOTO Gata
	Gata:
	
END